package com.szholly.pro.watergas_server.ps.schedule;

import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import lrq.common.MyLog;

import org.apache.log4j.Logger;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

public class CreateTransValidationDataWAG implements Job{

	static Logger log;
	@Override
	public void execute(JobExecutionContext arg0) throws JobExecutionException {
		
		if(log == null){
			log = MyLog.getLogger("CreateTransValidationData");
		}
		 
		try {
		   Calendar cal=Calendar.getInstance();
		   Calendar realCal = Calendar.getInstance();
	       //生成传输有效率月数据
		   realCal.setTime(cal.getTime());  
	       CreateMonthTransValidateData(realCal);
		   
		} catch (Exception e) {
			log.error("生成传输有效率数据出错",e);
		}
	}
	
	/**
	 * 
	 * 根据monitor_data_month中的均值数据，生成并插入相应的水质类别数据（month_water_type）
	 * xwei
	 * 2017年05月17日
	 * @return
	 * @throws ParseException 
	 */
	private void CreateMonthTransValidateData(Calendar cal){
		log.info("开始生成月数据");
		try{
			//判断当前时间是否为1号
	        if(cal.get(Calendar.DAY_OF_MONTH) != 1)
	        {
	        	return;
	        }
	        
	        //获取开始时间与结束时间
			Date endTime = cal.getTime();
			cal.add(Calendar.MONTH, -1);
			Date startTime = cal.getTime();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd 00:00:00");
			String start = sdf.format(startTime);
			startTime = sdf.parse(start);
			
			SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd 23:59:59");
			SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			cal.setTime(endTime);
			cal.add(Calendar.DATE, -1);  //减1天
			
			
			endTime = cal.getTime();
			String end = sdf1.format(endTime);
			endTime = sdf2.parse(end);
			
			CreateTransValidateData(startTime,endTime);
		} catch(Exception e)
		{
			log.error("生成月数据出错",e);
		}
	}
	
	/*
	 *1、按区域统计工业污染源的传输有效率。2、统计有效传输率小于0.75的站点
	 *@param startTime开始时间
	 *		 endTime结束时间
	 */
	private void CreateTransValidateData(Date startTime,Date endTime)throws Exception
	{
		try
		{
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			DecimalFormat decimalFormat=new DecimalFormat("#.00##");
			String start = sdf.format(startTime);
			String end = sdf.format(endTime);
			List<String> dataText = getM2MDateText(endTime,1,"month");
			//单个站点
			//计算传输率 实收条数/应收条数
			int RECEIVABLE_DATANUM = 0;//应收条数
			int REAL_DATANUM=0;//实收条数
			//计算有效率 有效组数/应收组数
			int RECEIVABLEVALIDATE_PACKETNUM = 0;//应收组数
			int REALVALIDATE_PACKETNUM=0;//有效组数
			
			//按区域汇总
			//计算传输率 实收条数/应收条数
			int ALL_RECEIVABLE_DATANUM = 0;//应收条数
			int ALL_REAL_DATANUM=0;//实收条数
			//计算有效率 有效组数/应收组数
			int ALL_RECEIVABLEVALIDATE_PACKETNUM = 0;//应收组数
			int ALL_REALVALIDATE_PACKETNUM=0;//有效组数
			int MONITOR_COMPANY_NUMBER = 0;//国控企业数
			int MONITOR_SITE_NUMBER = 0;//国控监控点数
					
			// 创建一个数值格式化对象  			  
	        NumberFormat numberFormat = NumberFormat.getInstance();  	  
	        // 设置精确到小数点后2位  	  
	        numberFormat.setMaximumFractionDigits(3); 
			List<GlobalParameter> parmsOver = new ArrayList<>();//用于保存超标的站点信息
			Map<String,List<GlobalParameter>> overSiteList = new HashMap<String,List<GlobalParameter>>();//保存超标的站点
			
			
			//传输率的计算
	        /**
	         * 第一步计算应收条数
	         */
	        //1.1 获取时间跨度
			String hours=getStationTime(start,end);
			int hoursInt=Integer.parseInt(hours);
			//1.2 几小时上传一条数据
			int hourInt=1;
			RECEIVABLEVALIDATE_PACKETNUM = hoursInt/hourInt;
			
			//统计废水的站点
			List<RowBase> stationIdListWW = getStationIdforWW();
			//统计废气的站点
			List<RowBase> stationIdListWG = getStationIdforWG();
			
			//废水
			if(stationIdListWW!=null && stationIdListWW.size()>0) {
				for(int K = 0;K<stationIdListWW.size();K++) {
					String id = stationIdListWW.get(K).getSafeString("CODE");
					List<RowBase> r=getpollParamforWW(id);
					int m=r.size();
					//1.4 应收条数
					RECEIVABLE_DATANUM=hoursInt*m/hourInt;
					/**
					 * 第二步计算实收条数
					 */
					//2.1 获取mn号
					if(r.size()==0)
					{
						continue;
					}
					String mn=r.get(0).getSafeString("DEVICE_NUM");
					//2.2 计算实收每个参数之和
					String parmname="";
					for(int i=0;i<r.size();i++){
						RowBase re=r.get(i);
						parmname+="'"+re.getSafeString("NAME")+"',";
					}
					parmname=parmname.substring(0,parmname.length()-1);
					REAL_DATANUM=getpollDataforWW(mn,parmname,start,end);
					
					//判断是否多传，实收不能大于应收
					if(REAL_DATANUM>RECEIVABLE_DATANUM){
						REAL_DATANUM=RECEIVABLE_DATANUM;
					}
					/**
					 * 计算停电时间段实收条数以及应收条数
					 */
					List<RowBase> noPowerList = getPowerOutageListByStation(startTime,endTime,id);
					int NOPOWER_REAL_DATANUM=0;
					int NOPOWER_RECEIVABLE_DATANUM=0;
					int NOPOWER_REALVALIDATE_PACKETNUM=0;
					int NOPOWER_RECEIVABLEVALIDATE_PACKETNUM=0;
					for(RowBase row:noPowerList)
					{
						String nopower_start = row.getSafeString("NO_POWER_START_TIME");
						String nopower_end = row.getSafeString("NO_POWER_END_TIME");
						Date s = sdf.parse(nopower_start);
						Date e = sdf.parse(nopower_end);
						nopower_start = sdf.format(s);
						nopower_end = sdf.format(e);
						if(s.compareTo(startTime)>=0&&e.compareTo(endTime)<=0)
						{
							//叠加应收的数据条数
							NOPOWER_RECEIVABLE_DATANUM+=Integer.parseInt(getStationTime(nopower_start,nopower_end))*m/hourInt;
							NOPOWER_RECEIVABLEVALIDATE_PACKETNUM+=Integer.parseInt(getStationTime(nopower_start,nopower_end))/hourInt;
							//叠加实收的数据条数
							NOPOWER_REAL_DATANUM+=getpollDataforWW(mn,parmname,nopower_start,nopower_end);
							NOPOWER_REALVALIDATE_PACKETNUM+=getValidDataforWW(mn,nopower_start,nopower_end);
						}
						else if(s.compareTo(startTime)<=0&&e.compareTo(endTime)<=0)
						{
							//叠加应收的数据条数
							NOPOWER_RECEIVABLE_DATANUM+=Integer.parseInt(getStationTime(sdf.format(startTime),nopower_end))*m/hourInt;
							NOPOWER_RECEIVABLEVALIDATE_PACKETNUM+=Integer.parseInt(getStationTime(sdf.format(startTime),nopower_end))/hourInt;
							//叠加实收的数据条数
							NOPOWER_REAL_DATANUM+=getpollDataforWW(mn,parmname,sdf.format(startTime),nopower_end);
							NOPOWER_REALVALIDATE_PACKETNUM+=getValidDataforWW(mn,sdf.format(startTime),nopower_end);
						}
						else if(s.compareTo(startTime)>=0&&e.compareTo(endTime)>=0)
						{
							//叠加应收的数据条数
							NOPOWER_RECEIVABLE_DATANUM+=Integer.parseInt(getStationTime(nopower_start,sdf.format(endTime)))*m/hourInt;
							NOPOWER_RECEIVABLEVALIDATE_PACKETNUM+=Integer.parseInt(getStationTime(nopower_start,sdf.format(endTime)))/hourInt;
							//叠加实收的数据条数
							NOPOWER_REAL_DATANUM+=getpollDataforWW(mn,parmname,nopower_start,sdf.format(endTime));
							NOPOWER_REALVALIDATE_PACKETNUM+=getValidDataforWW(mn,nopower_start,sdf.format(endTime));
							
						}
						else
						{
							//叠加应收的数据条数
							NOPOWER_RECEIVABLE_DATANUM+=Integer.parseInt(getStationTime(sdf.format(startTime),sdf.format(endTime)))*m/hourInt;
							NOPOWER_RECEIVABLEVALIDATE_PACKETNUM+=Integer.parseInt(getStationTime(sdf.format(startTime),sdf.format(endTime)))/hourInt;
							//叠加实收的数据条数
							NOPOWER_REAL_DATANUM+=getpollDataforWW(mn,parmname,sdf.format(startTime),sdf.format(endTime));
							NOPOWER_REALVALIDATE_PACKETNUM+=getValidDataforWW(mn,sdf.format(startTime),sdf.format(endTime));
						}
					}
					REAL_DATANUM=REAL_DATANUM-NOPOWER_REAL_DATANUM;
					RECEIVABLE_DATANUM=RECEIVABLE_DATANUM-NOPOWER_RECEIVABLE_DATANUM;
					/**
					 * 第三步 计算传输率
					 */
					float rate=0;
					if(RECEIVABLE_DATANUM>0)
					{
							rate=Float.valueOf(numberFormat.format((float)REAL_DATANUM/(float)RECEIVABLE_DATANUM));
					}
					String TRANSFER_RATE=decimalFormat.format(rate);
					//有效率的计算
					/**
					 *第一步计算应收组数 
					 */
					RECEIVABLEVALIDATE_PACKETNUM=hoursInt/hourInt;
					/**
					 * 第二步计算有效组数
					 */
					REALVALIDATE_PACKETNUM=getValidDataforWW(mn,start,end);
					//计算流量为零的数据组数
					int invalidate_packetnum = getB01ZeroDataforWW(mn,start,end);
					//统计数据修约后 没有上传凭证的数据组数
					int unLoadCertificatePacketNum = getUnLoadCertificatePacketNumforWW(mn,start,end);
					//有效组数不能大于应收组数
					if(REALVALIDATE_PACKETNUM>RECEIVABLEVALIDATE_PACKETNUM){
						REALVALIDATE_PACKETNUM=RECEIVABLEVALIDATE_PACKETNUM;
					}
					//获取停产停电故障时间产生的数据组数（这种情况要从实际有效组数中减去）
					int ERROR_PACKETNUM=0;
					
					//有效组数=实际应该有效数组个数-流量为0数组个数-修约后没有上传凭证的数据组数-停产停电时间段的数据组数
					REALVALIDATE_PACKETNUM = REALVALIDATE_PACKETNUM-invalidate_packetnum-ERROR_PACKETNUM-unLoadCertificatePacketNum;
					//可能会产生负数，负数的一律为0
					if(REALVALIDATE_PACKETNUM<0) {
						REALVALIDATE_PACKETNUM=0;
					}
					//停产停电的情况也要从应收数据组数中减去停电未传数据组数。
					RECEIVABLEVALIDATE_PACKETNUM = RECEIVABLEVALIDATE_PACKETNUM-ERROR_PACKETNUM-invalidate_packetnum;
					if(RECEIVABLEVALIDATE_PACKETNUM<0) {
						RECEIVABLEVALIDATE_PACKETNUM=0;
					}
					REALVALIDATE_PACKETNUM = REALVALIDATE_PACKETNUM-NOPOWER_REALVALIDATE_PACKETNUM;
					RECEIVABLEVALIDATE_PACKETNUM = RECEIVABLEVALIDATE_PACKETNUM-NOPOWER_RECEIVABLEVALIDATE_PACKETNUM;
					/**
					 * 第三步计算有效率
					 */
					float rateVali=0;
					if(RECEIVABLEVALIDATE_PACKETNUM>0)
					{
						rateVali=Float.valueOf(numberFormat.format((float)REALVALIDATE_PACKETNUM/(float)RECEIVABLEVALIDATE_PACKETNUM));
					}
					String VALIDATE_RATE=decimalFormat.format(rateVali);
					//传输有效率计算
					float rateAll=rate*rateVali;
					String Allrate=decimalFormat.format(rateAll);
					
					String insertSql ="insert into data_trans_validation_over\n" +
							"  (station_id, date_text, real_datanum, receivable_datanum, transfer_rate, receivablevalidate_packetnum, realvalidate_packetnum, validate_rate, transfer_validate_rate,company_name,site_name,privince_code,city_code,area_code,monitor_rank,station_category)\n" + 
							"values\n" + 
							"  (@STATION_ID, @v_data_text, @v_real_datanum, @v_receivable_datanum, @v_transfer_rate, @v_receivablevalidate_packetnum, @v_realvalidate_packetnum, @v_validate_rate, @v_transfer_validate_rate,@v_company_name,@v_site_name,@v_privince_code,@v_city_code,@v_area_code,@v_monitor_rank,@v_station_category)";
					
					List<GlobalParameter> parmsWW = new ArrayList<>();
					parmsWW.add(new GlobalParameter("STATION_ID", id));
					parmsWW.add(new GlobalParameter("v_privince_code", stationIdListWW.get(K).getSafeString("PROVINCE_CODE")));
					parmsWW.add(new GlobalParameter("v_city_code", stationIdListWW.get(K).getSafeString("CITY_CODE")));
					parmsWW.add(new GlobalParameter("v_area_code", stationIdListWW.get(K).getSafeString("AREA_CODE")));
					parmsWW.add(new GlobalParameter("v_data_text", dataText.get(0)));
					parmsWW.add(new GlobalParameter("v_company_name", stationIdListWW.get(K).getSafeString("COMNAME")));
					parmsWW.add(new GlobalParameter("v_site_name", stationIdListWW.get(K).getSafeString("SITENAME")));
					parmsWW.add(new GlobalParameter("v_data_text", dataText.get(0)));
					parmsWW.add(new GlobalParameter("v_real_datanum", REAL_DATANUM));
					parmsWW.add(new GlobalParameter("v_receivable_datanum", RECEIVABLE_DATANUM));
					parmsWW.add(new GlobalParameter("v_transfer_rate", TRANSFER_RATE));
					parmsWW.add(new GlobalParameter("v_receivablevalidate_packetnum", RECEIVABLEVALIDATE_PACKETNUM));
					parmsWW.add(new GlobalParameter("v_realvalidate_packetnum", REALVALIDATE_PACKETNUM));
					parmsWW.add(new GlobalParameter("v_validate_rate", VALIDATE_RATE));
					parmsWW.add(new GlobalParameter("v_transfer_validate_rate", Allrate));
					parmsWW.add(new GlobalParameter("v_monitor_rank", stationIdListWW.get(K).getSafeString("MONITOR_RANK")));
					parmsWW.add(new GlobalParameter("v_station_category", stationIdListWW.get(K).getSafeString("STATION_CATEGORY")));
					
					try{
						DataUtil.ExecuteUpdateSql(insertSql, parmsWW);
					}catch(Exception e){
						e.printStackTrace();
					}
				}
				
			}
			
			//废气
			if(stationIdListWG!=null && stationIdListWG.size()>0) {
				for(int K = 0;K<stationIdListWG.size();K++) {
					String id = stationIdListWG.get(K).getSafeString("CODE");
					List<RowBase> r=getpollParamforWG(id);
					int m=r.size();
					//1.4 应收条数
					RECEIVABLE_DATANUM=hoursInt*m/hourInt;
					/**
					 * 第二步计算实收条数
					 */
					//2.1 获取mn号
					if(r.size()==0)
					{
						continue;
					}

					String mn=r.get(0).getSafeString("DEVICE_NUM");
					//2.2 计算实收每个参数之和
					REAL_DATANUM=0;//实收条数
					String parmname="";
					for(int i=0;i<r.size();i++){
						RowBase re=r.get(i);
						parmname+="'"+re.getSafeString("NAME")+"',";
					}
					parmname=parmname.substring(0,parmname.length()-1);
					REAL_DATANUM=getpollDataforWG(mn,parmname,start,end);
					
					//判断是否多传，实收不能大于应收
					if(REAL_DATANUM>RECEIVABLE_DATANUM){
						REAL_DATANUM=RECEIVABLE_DATANUM;
					}
					/**
					 * 计算停电时间段实收条数以及应收条数
					 */
					List<RowBase> noPowerList = getPowerOutageListByStation(startTime,endTime,id);
					int NOPOWER_REAL_DATANUM=0;
					int NOPOWER_RECEIVABLE_DATANUM=0;
					int NOPOWER_REALVALIDATE_PACKETNUM=0;
					int NOPOWER_RECEIVABLEVALIDATE_PACKETNUM=0;
					for(RowBase row:noPowerList)
					{
						String nopower_start = row.getSafeString("NO_POWER_START_TIME");
						String nopower_end = row.getSafeString("NO_POWER_END_TIME");
						Date s = sdf.parse(nopower_start);
						Date e = sdf.parse(nopower_end);
						nopower_start = sdf.format(s);
						nopower_end = sdf.format(e);
						if(s.compareTo(startTime)>=0&&e.compareTo(endTime)<=0)
						{
							//叠加应收的数据条数
							NOPOWER_RECEIVABLE_DATANUM+=Integer.parseInt(getStationTime(nopower_start,nopower_end))*m/hourInt;
							NOPOWER_RECEIVABLEVALIDATE_PACKETNUM+=Integer.parseInt(getStationTime(nopower_start,nopower_end))/hourInt;
							//叠加实收的数据条数
							NOPOWER_REAL_DATANUM+=getpollDataforWG(mn,parmname,nopower_start,nopower_end);
							NOPOWER_REALVALIDATE_PACKETNUM+=getValidDataforWG(mn,nopower_start,nopower_end);
						}
						else if(s.compareTo(startTime)<=0&&e.compareTo(endTime)<=0)
						{
							//叠加应收的数据条数
							NOPOWER_RECEIVABLE_DATANUM+=Integer.parseInt(getStationTime(sdf.format(startTime),nopower_end))*m/hourInt;
							NOPOWER_RECEIVABLEVALIDATE_PACKETNUM+=Integer.parseInt(getStationTime(sdf.format(startTime),nopower_end))/hourInt;
							//叠加实收的数据条数
							NOPOWER_REAL_DATANUM+=getpollDataforWG(mn,parmname,sdf.format(startTime),nopower_end);
							NOPOWER_REALVALIDATE_PACKETNUM+=getValidDataforWG(mn,sdf.format(startTime),nopower_end);
						}
						else if(s.compareTo(startTime)>=0&&e.compareTo(endTime)>=0)
						{
							//叠加应收的数据条数
							NOPOWER_RECEIVABLE_DATANUM+=Integer.parseInt(getStationTime(nopower_start,sdf.format(endTime)))*m/hourInt;
							NOPOWER_RECEIVABLEVALIDATE_PACKETNUM+=Integer.parseInt(getStationTime(nopower_start,sdf.format(endTime)))/hourInt;
							//叠加实收的数据条数
							NOPOWER_REAL_DATANUM+=getpollDataforWG(mn,parmname,nopower_start,sdf.format(endTime));
							NOPOWER_REALVALIDATE_PACKETNUM+=getValidDataforWG(mn,nopower_start,sdf.format(endTime));
							
						}
						else
						{
							//叠加应收的数据条数
							NOPOWER_RECEIVABLE_DATANUM+=Integer.parseInt(getStationTime(sdf.format(startTime),sdf.format(endTime)))*m/hourInt;
							NOPOWER_RECEIVABLEVALIDATE_PACKETNUM+=Integer.parseInt(getStationTime(sdf.format(startTime),sdf.format(endTime)))/hourInt;
							//叠加实收的数据条数
							NOPOWER_REAL_DATANUM+=getpollDataforWG(mn,parmname,sdf.format(startTime),sdf.format(endTime));
							NOPOWER_REALVALIDATE_PACKETNUM+=getValidDataforWG(mn,sdf.format(startTime),sdf.format(endTime));
						}
					}
					REAL_DATANUM=REAL_DATANUM-NOPOWER_REAL_DATANUM;
					RECEIVABLE_DATANUM=RECEIVABLE_DATANUM-NOPOWER_RECEIVABLE_DATANUM;
					/**
					 * 第三步 计算传输率
					 */
					float rate=0;
					if(RECEIVABLE_DATANUM>0)
					{
							rate=Float.valueOf(numberFormat.format((float)REAL_DATANUM/(float)RECEIVABLE_DATANUM));
					}
					String TRANSFER_RATE=decimalFormat.format(rate);
					//有效率的计算
					/**
					 *第一步计算应收组数 
					 */
					RECEIVABLEVALIDATE_PACKETNUM=hoursInt/hourInt;
					/**
					 * 第二步计算有效组数
					 */
					REALVALIDATE_PACKETNUM=getValidDataforWG(mn,start,end);
					//计算流量为零的数据组数
					int invalidate_packetnum = getB01ZeroDataforWG(mn,start,end);
					//统计数据修约后 没有上传凭证的数据组数
					int unLoadCertificatePacketNum = getUnLoadCertificatePacketNumforWG(mn,start,end);
					//有效组数不能大于应收组数
					if(REALVALIDATE_PACKETNUM>RECEIVABLEVALIDATE_PACKETNUM){
						REALVALIDATE_PACKETNUM=RECEIVABLEVALIDATE_PACKETNUM;
					}
					//获取停产停电故障时间产生的数据组数（这种情况要从实际有效组数中减去）
					int ERROR_PACKETNUM=0;
					
					//有效组数=实际应该有效数组个数-流量为0数组个数-修约后没有上传凭证的数据组数-停产停电时间段的数据组数
					REALVALIDATE_PACKETNUM = REALVALIDATE_PACKETNUM-invalidate_packetnum-ERROR_PACKETNUM-unLoadCertificatePacketNum;
					REALVALIDATE_PACKETNUM = REALVALIDATE_PACKETNUM-NOPOWER_REALVALIDATE_PACKETNUM;
					//可能会产生负数，负数的一律为0
					if(REALVALIDATE_PACKETNUM<0) {
						REALVALIDATE_PACKETNUM=0;
					}
					//停产停电的情况也要从应收数据组数中减去停电未传数据组数。
					RECEIVABLEVALIDATE_PACKETNUM = RECEIVABLEVALIDATE_PACKETNUM-ERROR_PACKETNUM-invalidate_packetnum;
					RECEIVABLEVALIDATE_PACKETNUM = RECEIVABLEVALIDATE_PACKETNUM-NOPOWER_RECEIVABLEVALIDATE_PACKETNUM;
					if(RECEIVABLEVALIDATE_PACKETNUM<0) {
						RECEIVABLEVALIDATE_PACKETNUM=0;
					}
	
					/**
					 * 第三步计算有效率
					 */
					float rateVali=0;
					if(RECEIVABLEVALIDATE_PACKETNUM>0)
					{
						rateVali=Float.valueOf(numberFormat.format((float)REALVALIDATE_PACKETNUM/(float)RECEIVABLEVALIDATE_PACKETNUM));
					}
					String VALIDATE_RATE=decimalFormat.format(rateVali);
					//传输有效率计算
					float rateAll=rate*rateVali;
					String Allrate=decimalFormat.format(rateAll);		
					
					String insertSql ="insert into data_trans_validation_over\n" +
							"  (station_id, date_text, real_datanum, receivable_datanum, transfer_rate, receivablevalidate_packetnum, realvalidate_packetnum, validate_rate, transfer_validate_rate,company_name,site_name,privince_code,city_code,area_code,monitor_rank,station_category)\n" + 
							"values\n" + 
							"  (@STATION_ID, @v_data_text, @v_real_datanum, @v_receivable_datanum, @v_transfer_rate, @v_receivablevalidate_packetnum, @v_realvalidate_packetnum, @v_validate_rate, @v_transfer_validate_rate,@v_company_name,@v_site_name,@v_privince_code,@v_city_code,@v_area_code,@v_monitor_rank,@v_station_category)";
					
					List<GlobalParameter> parmsWG = new ArrayList<>();
					parmsWG.add(new GlobalParameter("STATION_ID", id));
					parmsWG.add(new GlobalParameter("v_privince_code", stationIdListWG.get(K).getSafeString("PROVINCE_CODE")));
					parmsWG.add(new GlobalParameter("v_city_code", stationIdListWG.get(K).getSafeString("CITY_CODE")));
					parmsWG.add(new GlobalParameter("v_area_code", stationIdListWG.get(K).getSafeString("AREA_CODE")));
					parmsWG.add(new GlobalParameter("v_data_text", dataText.get(0)));
					parmsWG.add(new GlobalParameter("v_company_name", stationIdListWG.get(K).getSafeString("COMNAME")));
					parmsWG.add(new GlobalParameter("v_site_name", stationIdListWG.get(K).getSafeString("SITENAME")));
					parmsWG.add(new GlobalParameter("v_data_text", dataText.get(0)));
					parmsWG.add(new GlobalParameter("v_real_datanum", REAL_DATANUM));
					parmsWG.add(new GlobalParameter("v_receivable_datanum", RECEIVABLE_DATANUM));
					parmsWG.add(new GlobalParameter("v_transfer_rate", TRANSFER_RATE));
					parmsWG.add(new GlobalParameter("v_receivablevalidate_packetnum", RECEIVABLEVALIDATE_PACKETNUM));
					parmsWG.add(new GlobalParameter("v_realvalidate_packetnum", REALVALIDATE_PACKETNUM));
					parmsWG.add(new GlobalParameter("v_validate_rate", VALIDATE_RATE));
					parmsWG.add(new GlobalParameter("v_transfer_validate_rate", Allrate));
					parmsWG.add(new GlobalParameter("v_monitor_rank", stationIdListWG.get(K).getSafeString("MONITOR_RANK")));
					parmsWG.add(new GlobalParameter("v_station_category", stationIdListWG.get(K).getSafeString("STATION_CATEGORY")));
					
					try{
						DataUtil.ExecuteUpdateSql(insertSql, parmsWG);
					}catch(Exception e){
						e.printStackTrace();
					}
				}
			}
			
			//生成报表参数
			//获取省份sql
			String proSql="select t.s_domaincode,t.s_pid from data_defulatdomain t where t.s_domainname='行政区划' and t.i_jb='1'";		
			List<RowBase> proList=DataUtil.getSelectRows(proSql, null);
			for(RowBase row:proList){
				//获取城市SQL语句
				String citySql="select t.s_domaincode,t.s_pid from data_defulatdomain t where t.s_domainname='行政区划' and t.s_pid='"+row.get("S_DOMAINCODE")+"'";
				List<RowBase> cityList=DataUtil.getSelectRows(citySql, null);		
				for(RowBase cityRow:cityList){
					int city_overCompany=0;//企业超标记录数
					int city_overSite=0;//站点超标记录数
					int city_CCompany=0;//国控企业超标记录数
					int city_CSite=0;//国控站点超标记录数
					int city_real_datanum=0;//实收条数
					int city_receivable_datanum=0;//应收条数
					int city_realvalidate_packetnum=0;//实收组数
					int city_receivable_packetnum=0;//应收组数
					
					//获取区域
					String areaSql="select t.s_domaincode,t.s_pid from data_defulatdomain t where t.s_domainname='行政区划' and t.s_pid='"+cityRow.get("S_DOMAINCODE")+"'";
					List<RowBase> areaList=DataUtil.getSelectRows(areaSql, null);
					for(RowBase arearow:areaList){
						int area_overCompany=0;//企业超标记录数
						int area_overSite=0;//站点超标记录数
						int area_CCompany=0;//国控企业超标记录数
						int area_CSite=0;//国控站点超标记录数
						int area_real_datanum=0;//实收条数
						int area_receivable_datanum=0;//应收条数
						int area_realvalidate_packetnum=0;//实收组数
						int area_receivable_packetnum=0;//应收组数
						//获取区域站点
						String siteSql="select * from data_trans_validation_over t where t.date_text='"+dataText.get(0)+"'"+" and t.privince_code='"+row.get("S_DOMAINCODE")+"'"+" and t.city_code='"
						+cityRow.get("S_DOMAINCODE")+"'"+" and t.area_code='"+arearow.get("S_DOMAINCODE")+"'";
						List<RowBase> siteList=DataUtil.getSelectRows(siteSql, null);
						//整合接收的总条数
						for(RowBase siteRow:siteList){
							String monitor_rank = siteRow.getSafeString("MONITOR_RANK");
							if(monitor_rank.equals("1"))
							{
								area_CSite = area_CSite+1;
							}
							
							double transfer_validate_rate = siteRow.getSafeDouble("TRANSFER_VALIDATE_RATE");
							if(transfer_validate_rate<0.75)
							{
								area_overSite =area_overSite+1;
							}
							area_real_datanum=area_real_datanum+siteRow.getSafeInt("REAL_DATANUM");
							area_receivable_datanum=area_receivable_datanum+siteRow.getSafeInt("RECEIVABLE_DATANUM");
							area_realvalidate_packetnum+=siteRow.getSafeInt("REALVALIDATE_PACKETNUM");
							area_receivable_packetnum+=siteRow.getSafeInt("RECEIVABLEVALIDATE_PACKETNUM");
							
						}
						
						//判断国控企业数量
						String ccomnumSql="select count(t.company_name) as companytotal from data_trans_validation_over t where t.date_text='"+dataText.get(0)+"'"+" and t.privince_code='"+row.get("S_DOMAINCODE")+"'"+" and t.city_code='"
								+cityRow.get("S_DOMAINCODE")+"'"+" and t.area_code='"+arearow.get("S_DOMAINCODE")+"'"+" and t.monitor_rank='1'";
						List<RowBase> ccom=DataUtil.getSelectRows(ccomnumSql, null);
						area_CCompany= ccom.get(0).getSafeInt("companytotal");
						
						//判断超标企业数量
						String ocomnumSql="select count(t.company_name) as companytotal from data_trans_validation_over t where t.date_text='"+dataText.get(0)+"'"+" and t.privince_code='"+row.get("S_DOMAINCODE")+"'"+" and t.city_code='"
								+cityRow.get("S_DOMAINCODE")+"'"+" and t.area_code='"+arearow.get("S_DOMAINCODE")+"'"+" and t.TRANSFER_VALIDATE_RATE<0.75";
						List<RowBase> ocom=DataUtil.getSelectRows(ocomnumSql, null);
						area_overCompany= ocom.get(0).getSafeInt("companytotal");
								
						float trans =0;
						float validate =0;
						if(area_receivable_datanum>0)
						{
							trans =Float.valueOf(numberFormat.format((float)area_real_datanum/(float)area_receivable_datanum));
						}
						if(area_receivable_packetnum>0)
						{
							validate=Float.valueOf(numberFormat.format((float)area_realvalidate_packetnum/(float)area_receivable_packetnum));
						}
						String transvate=decimalFormat.format(trans);
						String validaterate=decimalFormat.format(validate);
						String rate=decimalFormat.format(trans*validate);
						String insertSql ="insert into data_trans_validation_area\n" +
								"  (company_num,site_num,company_num_over,site_num_over, date_text, all_real_datanum, all_receivable_datanum, trans_rate, all_receivable_packetnum, all_realvalidate_packetnum, validation_rate, transfer_validate_rate,privince_code,city_code,area_code)\n" + 
								"values\n" + 
								"  (@CCompany,@CSite,@overCompany,@overSite, @v_data_text, @v_all_real_datanum, @v_all_receivable_datanum, @v_trans_rate, @v_all_receivable_packetnum, @v_all_realvalidate_packetnum, @v_validate_rate, @v_transfer_validate_rate,@v_privince_code,@v_city_code,@v_area_code)";
						
						List<GlobalParameter> parms = new ArrayList<>();
						parms.add(new GlobalParameter("CCompany", area_CCompany));
						parms.add(new GlobalParameter("CSite", area_CSite));
						parms.add(new GlobalParameter("overCompany", area_overCompany));
						parms.add(new GlobalParameter("overSite", area_overSite));
						parms.add(new GlobalParameter("v_data_text", dataText.get(0)));
						parms.add(new GlobalParameter("v_all_real_datanum", area_real_datanum));
						parms.add(new GlobalParameter("v_all_receivable_datanum", area_receivable_datanum));
						parms.add(new GlobalParameter("v_trans_rate", transvate));
						parms.add(new GlobalParameter("v_all_receivable_packetnum", area_receivable_packetnum));
						parms.add(new GlobalParameter("v_all_realvalidate_packetnum", area_realvalidate_packetnum));
						parms.add(new GlobalParameter("v_validate_rate", validaterate));
						parms.add(new GlobalParameter("v_transfer_validate_rate", rate));
						parms.add(new GlobalParameter("v_privince_code", row.get("S_DOMAINCODE")));
						parms.add(new GlobalParameter("v_city_code", cityRow.get("S_DOMAINCODE")));
						parms.add(new GlobalParameter("v_area_code", arearow.get("S_DOMAINCODE")));
						
						try{
							DataUtil.ExecuteUpdateSql(insertSql, parms);
						}catch(Exception e){
							e.printStackTrace();
						}
						city_real_datanum+=area_real_datanum;
						city_receivable_datanum+=area_receivable_datanum;
						city_realvalidate_packetnum+=area_realvalidate_packetnum;
						city_receivable_packetnum+=area_receivable_packetnum;
						city_overCompany+=area_overCompany;
						city_overSite+=area_overSite;
						city_CCompany+=area_CCompany;
						city_CSite+=area_CSite;
						
					}
					float trans =0;
					float validate =0;
					if(city_receivable_datanum>0)
					{
						trans =Float.valueOf(numberFormat.format((float)city_real_datanum/(float)city_receivable_datanum));
					}
					if(city_receivable_packetnum>0)
					{
						validate=Float.valueOf(numberFormat.format((float)city_realvalidate_packetnum/(float)city_receivable_packetnum));
					}
					String transvate=decimalFormat.format(trans);
					String validaterate=decimalFormat.format(validate);
					String rate=decimalFormat.format(trans*validate);

					String insertSql ="insert into data_trans_validation_area\n" +
							"  (company_num,site_num,company_num_over,site_num_over, date_text, all_real_datanum, all_receivable_datanum, trans_rate, all_receivable_packetnum, all_realvalidate_packetnum, validation_rate, transfer_validate_rate,privince_code,city_code,area_code)\n" + 
							"values\n" + 
							"  (@CCompany,@CSite,@overCompany,@overSite, @v_data_text, @v_all_real_datanum, @v_all_receivable_datanum, @v_trans_rate, @v_all_receivable_packetnum, @v_all_realvalidate_packetnum, @v_validate_rate, @v_transfer_validate_rate,@v_privince_code,@v_city_code,@v_area_code)";
					
					List<GlobalParameter> parms = new ArrayList<>();
					parms.add(new GlobalParameter("CCompany", city_CCompany));
					parms.add(new GlobalParameter("CSite", city_CSite));
					parms.add(new GlobalParameter("overCompany", city_overCompany));
					parms.add(new GlobalParameter("overSite", city_overSite));
					parms.add(new GlobalParameter("v_data_text", dataText.get(0)));
					parms.add(new GlobalParameter("v_all_real_datanum", city_real_datanum));
					parms.add(new GlobalParameter("v_all_receivable_datanum", city_receivable_datanum));
					parms.add(new GlobalParameter("v_trans_rate",transvate ));
					parms.add(new GlobalParameter("v_all_receivable_packetnum", city_receivable_packetnum));
					parms.add(new GlobalParameter("v_all_realvalidate_packetnum", city_realvalidate_packetnum));
					parms.add(new GlobalParameter("v_validate_rate", validaterate));
					parms.add(new GlobalParameter("v_transfer_validate_rate", rate));
					parms.add(new GlobalParameter("v_privince_code", row.get("S_DOMAINCODE")));
					parms.add(new GlobalParameter("v_city_code", cityRow.get("S_DOMAINCODE")));
					parms.add(new GlobalParameter("v_area_code", null));
					
					try{
						DataUtil.ExecuteUpdateSql(insertSql, parms);
					}catch(Exception e){
						e.printStackTrace();
					}
				}				
			}
			
		}
		catch(Exception e)
		{
			log.error("生成传输有效率报表数据出错", e);
		}
		
	}
	
	

	/**
	 * 获取环比时间点集
	 * gislin
	 * 2016年8月27日
	 * @param endDate
	 * @param dateLength
	 * @param cycleTypeStr day,week,month,quarter,year
	 * @return
	 * @throws ParseException
	 */
	private List<String> getM2MDateText(Date endDate,int dateLength,String cycleTypeStr) throws ParseException{
		
		List<String> dateTexts = new ArrayList<>();
		List<String> resultDateTexts = new ArrayList<>();
		
		Calendar currentCal = Calendar.getInstance();
		currentCal.setTime(endDate);
		
		//如果是季度的话，把月置为每个季度的第一个月
		if("quarter".equals(cycleTypeStr)){
			int month = currentCal.get(Calendar.MONTH);
			if(month <= 3){
				currentCal.set(Calendar.MONTH, 1);
			}
			else if(month <= 6){
				currentCal.set(Calendar.MONTH, 4);
			}
			else if(month <= 9){
				currentCal.set(Calendar.MONTH, 7);
			}
			else if(month <= 12){
				currentCal.set(Calendar.MONTH, 10);
			}
		}
		
		for(int i=0;i<dateLength;i++){
			
			int year = currentCal.get(Calendar.YEAR);
			int week = currentCal.get(Calendar.WEEK_OF_YEAR);
			
			String curMonth = "";
			if("week".equals(cycleTypeStr)){
				dateTexts.add(year + "年" + week+"周");
				currentCal.add(Calendar.WEEK_OF_YEAR, -1);
			}
			else if("month".equals(cycleTypeStr)){
				curMonth = String.format("%0" + 2 + "d", currentCal.get(Calendar.MONTH)+1);
				dateTexts.add(year + "年" + curMonth+"月");
				currentCal.add(Calendar.MONTH, -1);
			}
		}
		for(int i=dateTexts.size()-1;i>=0;i--){
			resultDateTexts.add(dateTexts.get(i));
		}
		
		return resultDateTexts;
	}
	
	/**
	 * 获取站点运行时间，默认站点24小时都在运行
	 * @param st 开始时间
	 * @param et 结束时间
	 * @return
	 */
	public String getStationTime(String st,String et){
		//一小时有多少毫秒
		 long nh = 1000 * 60 * 60;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date ct_date=new Date ();
		Date et_date=new Date();
		try {
			ct_date = sdf.parse(st);
			et_date = sdf.parse(et);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		// 获得两个时间的毫秒时间差异
	    long diff = et_date.getTime() - ct_date.getTime();
	    // 计算差多少小时
	    long counthour = diff / nh;
	    //如果是23:59:59就加1
	    if(et.contains("23:59:59")){
	    	counthour=counthour+1;
	    }
	    return counthour+"";
	}
	/**
	 * 获取站点信息(废水)
	 * @param stationId
	 * @return
	 */
	public List<RowBase> getStationIdforWW(){
		String sqlforWW="select t.code,t.name as sitename,t.province_code,t.city_code,t.area_code,t1.name as comname,t.station_category,t.monitor_rank from WWSZ.BC_SITE_INFO t left join WWSZ.bc_enterprise_attr t1 on t.wwtp_id=t1.exp_no where t.station_category in ('2')";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sqlforWW, parms);
		return row;
	}
	/**
	 * 获取站点信息(废气)
	 * @param stationId
	 * @return
	 */
	public List<RowBase> getStationIdforWG(){
		String sqlforWW="select t.code,t.name as sitename,t.province_code,t.city_code,t.area_code,t1.name as comname,t.station_category,t.monitor_rank from BC_SITE_INFO t left join bc_enterprise_attr t1 on t.wwtp_id=t1.exp_no where t.station_category in ('4')";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sqlforWW, parms);
		return row;
	}
	
	/**
	 * 获取站点参数(废水)
	 * @param stationId
	 * @return
	 */
	public List<RowBase> getpollParamforWW(String stationId){
		String sql="select t.name,t1.device_num,t.site_id from WWSZ.BC_SITE_POLL t left join WWSZ.BC_COLLECTION_INFO t1 on t.site_id=t1.site_id where t.site_id='"+stationId+"' group by t.name,t1.device_num,t.site_id";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		return row;
	}
	
	/**
	 * 获取站点参数(废气)
	 * @param stationId
	 * @return
	 */
	public List<RowBase> getpollParamforWG(String stationId){
		String sql="select t.name,t1.device_num,t.site_id from BC_SITE_POLL t left join BC_COLLECTION_INFO t1 on t.site_id=t1.site_id where t.site_id='"+stationId+"'  group by t.name,t1.device_num,t.site_id";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		return row;
	}
	
	/**
	 * 获取时间段内参数条数(废水)
	 * @param mn
	 * @param parmname
	 * @param st
	 * @param et
	 * @return
	 */
	public int getpollDataforWW(String mn,String parmname,String st ,String et){
		String sql="select t.*  from WWSZ.BD_MO_HOUR t where t.mn='"+mn+"' and t.parmname in ("+parmname+") and t.datatime >= to_date('"
				   +st+"','yyyy-MM-dd hh24:mi:ss') and t.datatime <= to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		return row.size();
	}
	
	/**
	 * 获取时间段内参数条数(废气)
	 * @param mn
	 * @param parmname
	 * @param st
	 * @param et
	 * @return
	 */
	public int getpollDataforWG(String mn,String parmname,String st ,String et){
		String sql="select t.*  from BD_MO_HOUR t where t.mn='"+mn+"' and t.parmname in ("+parmname+") and t.datatime >= to_date('"
				   +st+"','yyyy-MM-dd hh24:mi:ss') and t.datatime <= to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		return row.size();
	}
	
	/**
	 * 获取有效组数()废水
	 * @param mn
	 * @param st
	 * @param et
	 * @return
	 */
	public int getValidDataforWW(String mn,String st ,String et){
		String sql="select count(*) as sum  from (select distinct (t.mn||to_char(t.datatime,'yyyy-MM-dd HH24:mi:ss')) as guid from WWSZ.BD_MO_HOUR t where t.flag ='N' and t.mn='"+mn+"' and t.datatime >= to_date('"
				   +st+"','yyyy-MM-dd hh24:mi:ss') and t.datatime <= to_date('"+et+"','yyyy-MM-dd hh24:mi:ss'))";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		int result=0;
		if(row.size()>0){
			result=row.get(0).getSafeInt("SUM");
		}
		return result;
	}
	
	/**
	 * 获取有效组数()废气
	 * @param mn
	 * @param st
	 * @param et
	 * @return
	 */
	public int getValidDataforWG(String mn,String st ,String et){
		String sql="select count(*) as sum  from (select distinct (t.mn||to_char(t.datatime,'yyyy-MM-dd HH24:mi:ss')) as guid from BD_MO_HOUR t where t.flag ='N' and t.mn='"+mn+"' and t.datatime >= to_date('"
				   +st+"','yyyy-MM-dd hh24:mi:ss') and t.datatime <= to_date('"+et+"','yyyy-MM-dd hh24:mi:ss'))";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		int result=0;
		if(row.size()>0){
			result=row.get(0).getSafeInt("SUM");
		}
		return result;
	}
	
	/**
	 * 获取流量为0的数据条数(废水)
	 * @param mn
	 * @param st
	 * @param et
	 * @return
	 */
	private int getB01ZeroDataforWW(String mn,String st ,String et){
		String pollSql = "select t2.name\n" +
						"  from WWSZ.bc_collection_info t\n" + 
						"  left join WWSZ.bc_site_poll t2 on t.site_id = t2.site_id\n" + 
						" where t.device_num = '"+mn+"'";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> pollrow = DataUtil.getSelectRows(pollSql, parms);
		//调整流量筛选策略，如果该站点有瞬时流量，则根据瞬时流量来统计流量为0的条数，否则根据累计流量统计
		String paramName="B01TOTAL";
		if(pollrow!=null && pollrow.size()>0) {
			for(int i = 0;i < pollrow.size();i++) {
				RowBase row = new RowBase();
				row = pollrow.get(i);
				String pollName = row.getSafeString("NAME");
				if("B01".equals(pollName)){
					paramName = "B01";
					break;
				}
			}
		}
		String sql="select t.parmname,t.datatime,t.avg  from  WWSZ.bd_mo_hour t where t.flag ='N' and t.mn='"+mn+"' and t.datatime >= to_date('\n"
				   +st+"','yyyy-MM-dd hh24:mi:ss') and t.datatime <= to_date('"+et+"','yyyy-MM-dd hh24:mi:ss') \n"
				   + " and t.avg=0 and t.parmname='"+paramName+"'\n"
				   + " group by t.parmname,t.datatime,t.avg";
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		int result=0;
		if(row.size()>0){
			result=row.size();
		}
		return result;
	}
	
	/**
	 * 获取流量为0的数据条数(废气)
	 * @param mn
	 * @param st
	 * @param et
	 * @return
	 */
	private int getB01ZeroDataforWG(String mn,String st ,String et){
		String pollSql = "select t2.name\n" +
						"  from bc_collection_info t\n" + 
						"  left join bc_site_poll t2 on t.site_id = t2.site_id\n" + 
						" where t.device_num = '"+mn+"'";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> pollrow = DataUtil.getSelectRows(pollSql, parms);
		//调整流量筛选策略，如果该站点有瞬时流量，则根据瞬时流量来统计流量为0的条数，否则根据累计流量统计
		String paramName="B01TOTAL";
		if(pollrow!=null && pollrow.size()>0) {
			for(int i = 0;i < pollrow.size();i++) {
				RowBase row = new RowBase();
				row = pollrow.get(i);
				String pollName = row.getSafeString("NAME");
				if("B01".equals(pollName)){
					paramName = "B01";
					break;
				}
			}
		}
		String sql="select t.parmname,t.datatime,t.avg  from  bd_mo_hour t where t.flag ='N' and t.mn='"+mn+"' and t.datatime >= to_date('\n"
				   +st+"','yyyy-MM-dd hh24:mi:ss') and t.datatime <= to_date('"+et+"','yyyy-MM-dd hh24:mi:ss') \n"
				   + " and t.avg=0 and t.parmname='"+paramName+"'\n"
				   + " group by t.parmname,t.datatime,t.avg";
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		int result=0;
		if(row.size()>0){
			result=row.size();
		}
		return result;
	}
	
	/**
	 * 获取修约后未上传凭证的数据组数
	 * @param mn
	 * @param st
	 * @param et
	 * @return
	 */
	public int getUnLoadCertificatePacketNumforWW(String mn,String st ,String et){
		String sql="select t.datatime  from  WWSZ.BD_MO_HOUR t where t.flag ='N' and t.mn='"+mn+"' and t.datatime >= to_date('\n"
				   +st+"','yyyy-MM-dd hh24:mi:ss') and t.datatime <= to_date('"+et+"','yyyy-MM-dd hh24:mi:ss') \n"+
				   " and t.data_from>0 and t.modify_certificate_id is null \n"+
				   " and t.modify_date is not null \n"+
				   " group by t.datatime ";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		int result=0;
		if(row.size()>0){
			result=row.size();
		}
		return result;
	}
	
	/**
	 * 获取修约后未上传凭证的数据组数
	 * @param mn
	 * @param st
	 * @param et
	 * @return
	 */
	public int getUnLoadCertificatePacketNumforWG(String mn,String st ,String et){
		String sql="select t.datatime  from  BD_MO_HOUR t where t.flag ='N' and t.mn='"+mn+"' and t.datatime >= to_date('\n"
				   +st+"','yyyy-MM-dd hh24:mi:ss') and t.datatime <= to_date('"+et+"','yyyy-MM-dd hh24:mi:ss') \n"+
				   " and t.data_from>0 and t.modify_certificate_id is null \n"+
				   " and t.modify_date is not null \n"+
				   " group by t.datatime ";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		int result=0;
		if(row.size()>0){
			result=row.size();
		}
		return result;
	}
	
	public List<RowBase> getPowerOutageListByStation(Date start,Date end,String stationID)
	{
	    String querySql="select *  from BC_SNP_INFO t where t.station_id=@station_id \n"
	    		+" and  t.no_power_start_time <=@endDate\n"
	    		+" and  t.no_power_end_time >=@startDate\n";
	    querySql+=" order by t.no_power_start_time";
	    
		List<GlobalParameter> parms = new ArrayList<>();
		parms.add(new GlobalParameter("station_id", stationID));
		parms.add(new GlobalParameter("startDate", start));
		parms.add(new GlobalParameter("endDate", end));
	    List<RowBase> result = DataUtil.getSelectRows(querySql, parms);
	    return result;
	}
}
